﻿
IF EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'OTEntregaCalculaCosto')
	BEGIN
		DROP  Procedure  OTEntregaCalculaCosto
	END
GO

/*
	Ejemplo:
	DECLARE @monto AS MONEY
	EXEC OTEntregaCalculaCosto 6, @monto OUT
	SELECT @monto AS 'Total calculado para la entrega' 
	Nota: Este SP esta previsto para mostrar el desgloce de los costos

*/
CREATE PROCEDURE [dbo].[OTEntregaCalculaCosto]
	(	
		@EntregaId	AS INT,		
		@CostoTotal AS MONEY  OUT
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @OTDetalle Table
		(EntregaId		INT,
		 Total			MONEY)

	DECLARE @OTGastosIndirectos Table
		(EntregaId		INT,
		 Monto			MONEY)

	DECLARE @OTRubro Table
		(EntregaId		INT,
		 RubroNombre	VARCHAR(50),
		 Monto			MONEY)
	

	-- OT Detalle (Mano de obra)
	INSERT INTO @OTDetalle
		SELECT OTEntregaId, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o		
		INNER JOIN Lit_Operario op ON o.operarioId = op.id
		WHERE OTEntregaId = @EntregaId	  
		GROUP BY OTEntregaId
		UNION
		SELECT OTEntregaId, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoAyudantexHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o		
		INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
		WHERE OTEntregaId = @EntregaId	  
		GROUP BY OTEntregaId


	INSERT INTO @OTRubro
		SELECT EntregaId, 'Mano de Obra Utilizada' As RubroNombre, SUM(Total) AS Monto
		FROM @OTDetalle
		GROUP BY EntregaId
	
	-- OT Material
	INSERT INTO @OTRubro
		SELECT OTEntregaId, 'Materiales Utilizados' As RubroNombre, SUM(CostoArticuloTotal) AS Monto
		FROM Lit_OTMaterial m		
		INNER JOIN Lit_OTEntrega e ON m.OTEntregaId = e.Id
		INNER JOIN Articulo a ON a.Emp_Id = e.EmpresaId AND m.ArticuloId = a.Art_id
		WHERE OTEntregaId = @EntregaId
		GROUP BY OTEntregaId

	-- OT Gastos Directos
	INSERT INTO @OTRubro
		SELECT OTEntregaId, 'Gastos Directos Aplicados' As RubroNombre, SUM(otg.Monto) As Monto
		FROM Lit_OTGasto otg
		INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
		WHERE OTEntregaId = @EntregaId AND g.Tipo = 'D'
		GROUP BY OTEntregaId

	-- OT Gastos Indirectos
	INSERT INTO @OTGastosIndirectos
		SELECT OTEntregaId, otg.Monto
		FROM Lit_OTGasto otg
		INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
		WHERE OTEntregaId = @EntregaId AND g.Tipo = 'I'
		UNION
		SELECT Id, ISNULL(ManoObraIndirecta,0) As Monto
		FROM Lit_OTEntrega
		WHERE Id = @EntregaId
		AND ManoObraIndirecta IS NOT NULL
		AND ManoObraIndirecta > 0

	INSERT INTO @OTRubro
		SELECT EntregaId, 'Gastos Indirectos Aplicados' As RubroNombre, SUM(Monto) As Monto
		FROM @OTGastosIndirectos
		GROUP BY EntregaId

	INSERT INTO @OTRubro
		SELECT OTEntregaId, 'Máquinas Utilizadas' As RubroNombre, SUM(CostoMaquina) AS Monto
		FROM Lit_OTDetalle o
		WHERE OTEntregaId = @EntregaId
		GROUP BY OTEntregaId

	SELECT @CostoTotal = SUM(Monto)
	FROM @OTRubro
           	            
	SET NOCOUNT OFF
END 
GO